# coding:utf-8

"""
从excel文件生成JSON文件

@author:李志强 JOHNNY LI
@2021.8
@shanghai
"""

import json
import pandas as pd
import random
import copy

SYSTEM_NAMES_KEYWORDS = ["SYSTEM_A", "SYSTEM_B", "SYSTEM_C", "SYSTEM_D"]  # 文件名中包含的关键字
SYSTEM_NAMES_MAP = {"SYSTEM A": "SYSTEM_A"}
SYSTEM_NAMES = ["SYSTEM_A", "SYSTEM_B",
                "SYSTEM_C", "SYSTEM_D"]  # 系统名以此为准
SYSTEM_UNDEFINED = "UNDEFINED"
SYSTEM_IGNORE = ["SYSTEM_E", "SYSTEM_F"]  # 不统计关系的系统

# 生成分类
CATEGORIES = []
for system_name in SYSTEM_NAMES:
    cat = {"name": system_name, "symbol": "diamond"}
    CATEGORIES.append(cat)


# 随机颜色
def randomcolor_func():
    color_char = ['1', '2', '3', '4', '5', '6', '7', '8', '9', 'A', 'B', 'C', 'D', 'E', 'F']
    color_code = ""
    for i in range(6):
        color_code += color_char[random.randint(0, 14)]  # randint包括前后节点0和14
    return "#" + color_code


# 随机坐标
# 生成随机数，浮点类型
def generate_position(n):
    #  n = 10
    for i in range(n):
        x = round(random.uniform(-2000, 2000), 5)  # 一定范围内的随机数，范围可变
        y = round(random.uniform(-2000, 2000), 5)  # 控制随机数的精度round(数值，精度)
    return x, y


def generate_node_data(info_name, target_system, table_en_name, table_cn_name, supply_type):
    node_dict = {r"src_system": info_name, r"target_system": target_system, r"table_en_name": table_en_name,
                 r"table_cn_name": table_cn_name, r"supply_type": supply_type}
    return node_dict


def read_data_from_excel(filename):
    """从excel读取数据，数据项作为一个节点（信息类）"""
    out = []

    # 系统节点
    system_name = get_system_name(filename)

    # 读取"对外供应数据表清单"
    # 目的系统名称	表英文名	表中文名称	数据供应方式（交易线，数据线）
    data = pd.read_excel(filename, sheet_name="对外供应数据表清单", engine="openpyxl")
    for i in range(len(data)):
        row = data.iloc[i]
        info_name = system_name + "#" + trim_node_name(row[2])  # 类似 A#财务数据 这种格式

        # 处理以/分隔的多个系统
        system_target_list = []
        if "/" in row[0]:
            systems = str.split(row[0], "/")
            for system in systems:
                system_target = get_system_name(system)
                if system_target not in SYSTEM_IGNORE:
                    system_target_list.append(system_target)
        else:
            system_target = get_system_name(row[0])
            if system_target not in SYSTEM_IGNORE:
                system_target_list.append(system_target)

        for system_target in system_target_list:
            tmp_node = copy.deepcopy(generate_node_data(info_name, system_target, row[1], row[2], row[3]))
            out.append(tmp_node)

    # 读取"所依赖外部系统数据表清单"
    # 目的系统名称	表英文名	表中文名	数据供应方式（交易线，数据线）
    data2 = pd.read_excel(filename, sheet_name="所依赖外部系统数据表清单", engine="openpyxl")
    for i in range(len(data2)):
        row = data2.iloc[i]

        # 处理以/分隔的多个系统
        system_src_list = []
        if "/" in row[0]:
            systems_src = str.split(row[0], "/")
            for system in systems_src:
                system_src_list.append(system)
        else:
            system_src_list.append(row[0])

        system_target = system_name
        for system in system_src_list:
            system_name_src = get_system_name(system)
            if system_name_src not in SYSTEM_IGNORE:
                info_name = system_name_src + "#" + trim_node_name(row[2])
                tmp_node = copy.deepcopy(generate_node_data(info_name, system_target, row[1], row[2], row[3]))
                out.append(tmp_node)

    return out


def get_system_name(filepath):
    """获取系统类节点经过映射的名称"""
    temp_name = filepath
    flag = False
    # step 1
    for name in SYSTEM_NAMES_KEYWORDS:
        if name in filepath or (name.upper() in filepath) or (name.lower() in filepath):
            if name in SYSTEM_NAMES_MAP.keys():
                temp_name = SYSTEM_NAMES_MAP[name]  # 如果有映射，取映射的值
            else:
                temp_name = name
            flag = True
            break

    # step 2
    if not flag:
        if temp_name in SYSTEM_NAMES_MAP.keys():
            temp_name = SYSTEM_NAMES_MAP[temp_name]
        else:
            temp_name = SYSTEM_UNDEFINED
            # debug
            print(filepath)

    return temp_name


def trim_node_name(name):
    """去除信息类节点中的类如 信息、信息表、表 等后缀"""
    return str(name).rstrip("信息").rstrip("主表").rstrip("表").rstrip("信息")


def get_linestyle(supply_type):
    """根据供应类型获取线条类型"""
    if supply_type == "交易线":
        return "dashed"
    elif supply_type == "数据线":
        return "solid"
    else:
        return "solid"


def get_all_node_data(data_dir):
    all_node_data = []

    import os
    for root, dirs, files in os.walk(data_dir):
        for file in files:
            # get node data
            filepath = os.path.join(root, file)
            all_node_data.extend(read_data_from_excel(filepath))

    return all_node_data


def create_node_json(all_node_data):
    # 自定义节点
    address_dict = {"nodes": [], "edges": [], "categories": CATEGORIES}
    node_dict = {
        "color": "",
        "label": "",
        "attributes": {},
        "y": None,
        "x": None,
        "id": "",
        "size": None,
        "symbol": "circle",
        "category": None
    }
    edge_dict = {
        "sourceID": "",
        "attributes": {},
        "type_": "",  # 边是实线还是虚线
        "targetID": "",
        "size": None
    }
    # cat = {
    #     "name": "",
    #     "symbol": "diamond"
    # }
    #
    # # 生成分类
    # for system_name in SYSTEM_NAMES:
    #     cat["name"] = system_name
    #     tmp_cat = copy.deepcopy(cat)
    #     address_dict["categories"].append(tmp_cat)

    # 获取节点列表和边
    nodes = []
    nodes_weight_info = {}  # {"a":10,"b":20} 记录节点权重
    for anode in all_node_data:
        if anode["src_system"] not in nodes_weight_info.keys():
            nodes_weight_info[anode["src_system"]] = 10  # 初始权重为4
        else:
            nodes_weight_info[anode["src_system"]] += 5  # 权重增加10

        if anode["target_system"] not in nodes_weight_info.keys():
            nodes_weight_info[anode["target_system"]] = 10

        edge_dict[r"sourceID"] = anode["src_system"]
        edge_dict[r"targetID"] = anode["target_system"]
        # edge_dict[r"size"] = 10
        edge_dict[r"value"] = anode["table_en_name"]
        edge_dict[r"type_"] = get_linestyle(anode["supply_type"])
        # edge_dict[r"attributes"] = {"edge_label":"xxxx"}

        tmp_edge = copy.deepcopy(edge_dict)
        address_dict["edges"].append(tmp_edge)

    # 构建节点信息
    nodes = []  # 需要就行排序
    for node in nodes_weight_info.keys():
        color = randomcolor_func()
        node_dict[r"color"] = color
        node_dict[r"label"] = node
        x, y = generate_position(1)
        node_dict[r"y"] = y
        node_dict[r"x"] = x
        node_dict[r"id"] = node
        # node_dict[r"size"] = int(nodes_weight_info[node])

        # category
        end = None
        if "#" in node:
            end = node.index("#")
        system_of_node = node[0: end]
        # debug
        if system_of_node == "UNDEFINED":
            print(system_of_node)
        index = SYSTEM_NAMES.index(system_of_node)
        node_dict["category"] = index

        if node in SYSTEM_NAMES_KEYWORDS:
            node_dict[r"symbol"] = "diamond"
            node_dict[r"size"] = 50
        else:
            node_dict[r"symbol"] = "circle"
            node_dict[r"size"] = int(nodes_weight_info[node])

        tmp_node = copy.deepcopy(node_dict)
        nodes.append(tmp_node)

    # 对nodes排序，且添加排序后节点信息到最后结果
    nodes.sort(key=lambda a: a["label"])
    address_dict[r"nodes"].extend(nodes)

    return address_dict


if __name__ == '__main__':
    cur_dir = "files_excel"
    all_node_data = get_all_node_data(cur_dir)
    all_json = create_node_json(all_node_data)
    with open("all_node_data.json", "w", encoding='utf-8') as f:
        # json.dump(dict_, f) # 写为一行
        json.dump(all_json, f, indent=2, ensure_ascii=False)  # 写为多行
